With the proliferation of large transactional systems has come the need to rapidly access and store large volumes of database information. The need for efficient management of large volumes of information is further exacerbated by the exponential growth of the Internet and the plurality of end-users accessing data stored in large databases (an example of which is data stored in the World Wide Web).
Due to their ease of scalability and reduced processing overhead, databases are preferably implemented based on the relational database architecture. In a relational database, data and relationships are represented by a collection of tables in which each table is associated with a unique name or unique identifier. A row in a table represents the relationship among a set of data stored in the table. The storage representation of a row is called a record, and the storage representation of a column is called a field. Data is translated into a sequence of bytes and is then stored at the intersection of a row and a column of a table.
As the size of the stored data increases, the table is divided into partitions. In a multicomputer structure having an array of processors adapted to operate with shared memory systems, each partition of the table may be independently stored in non-contiguous memory locations thereby allowing pipelining and bulk parallel processing of the database information. Table partitions are managed independently by the Post-Relational Database Management System (PRDBMS) but the table data access remains unaffected.
Several known schemas exist for distributing data across partitions in memory systems. These partitioning schemas (also known as strategies) are tightly coupled with the physical implementation of the data model for the database system. One popular partitioning scheme uses a randomizing hashing function to horizontally or vertically partition the contents of a database (or of the table) across different memory systems. The database or the table may also be partitioned based on information not stored in the database, such information may include—for example—the site where the data was inserted, the user who inserted the data, and/or the application used to insert the data into the database.
Regardless of the known partitioning scheme used, large databases storing vast amounts of information present a challenge for efficient access and management of data located across many partitions.
Known PRDBMSs manage data that has been distributed across multiple partitions associated with database(s) and communicate this data to the end users. PRDBMSs consist of a collection of executable programs that enables users to access, modify, store or retrieve data associated with the database. Over the years, the Structured Query Language (SQL) interface—initially developed by IBM—has evolved to become the de facto database query language for accessing and modifying data stored in relational databases. The SQL interface facilitates database queries by building an index file which is associated with the stored data (in addition to storing the data in a data file related to the database). Database applications may access the entire contents of the database by submitting standard SQL query statements to the PRDBMS, and in turn, the PRDBMS compiles and executes those SQL queries against the database.
To efficiently access databases containing massive amounts of data, the PRDBMS must work with many different types of SQL query statements (such as SELECT, INSERT, UPDATE, DELETE, etc). To access data stored in partitioned relational databases, the PRDBMS must establish both a physical and a logical connection to the database partition where that data resides. The PRDBMS typically uses a database name and a server port to establish the physical connection to the database partition. To establish a logical connection, the PRDBMS resorts to using an index that is an ordered set of references to the records and fields in the table of that database. The index provides a direct path to the stored data through pointers that have been ordered based on keys associated with the index. A key is one of the fields of the record or one of the columns of a row. The keys may be organized into a partition map by a mapping function such as a hash function.
To retrieve and access data contained within a particular partition, the PRDBMS uses the supplied query predicates within an SQL statement to determine the optimal data access strategy. However, this process may become inefficient when managing massive amounts of stored data. Furthermore, large amounts of data typically must be first split before the split data can be loaded at desired database partition(s). This is commonly achieved by an application utility program provided by the PRDBMS, such as an AutoLoader utility program provided by the IBM DB2 database environment. The AutoLoader utility uses a hashing algorithm to split data into as many output sockets as there are database partitions. This utility then loads the output sockets across a set of database partitions. Data splitting may become overwhelming for utility programs when dealing with massive quantities of data.
Based on the foregoing, it is appreciated that data loading and access in PRDBMSs consumes a considerable amount of CPU, network, memory, and storage resources. Network resources can become a significant component of the overall SQL query statement processing costs for the PRDBMS. Although data can be managed in a parallel fashion, each partition in a partitioned database environment still requires a SQL query statement processing agent commonly referred to as the coordinator for executing an SQL query statement. Additional communication costs are incurred when the required data is not collocated with this coordinator. Network resources can be eliminated from SQL statement processing when the required data is collocated with the coordinator. Minimizing network resources may be a critical factor for scaling high volume transactional processing systems.
Another shortcoming especially encountered in legacy PRDBMSs is the lack of any optimization while executing database queries. High-level SQL queries are generally non-procedural in nature. When a query is presented to a legacy PRDBMS system, the query indicates what type of action to perform as opposed to how to go about performing the type of action (as set forth in the SQL query statement). Accordingly, data accessing in large partitioned databases may become unmanageable. Accordingly, a solution that addresses, at least in part, this and other shortcomings is desired.